package com.team9.fitness.mapper;

import com.team9.fitness.entity.CommunityPost;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

/**
 * 社区帖子数据访问层接口
 * 
 * 提供社区帖子数据的CRUD操作：
 * - 帖子管理：插入、更新、删除帖子
 * - 查询功能：支持按用户、ID查询，支持排序
 * - 统计功能：统计帖子的点赞、喜欢、评论数量
 * - 状态管理：支持用户点赞、喜欢状态查询
 */
@Mapper
public interface CommunityPostMapper {

    @Insert("INSERT INTO community_post (user_id, title, content, like_count, favorite_count, comment_count, created_at, updated_at) " +
            "VALUES (#{userId}, #{title}, #{content}, #{likeCount}, #{favoriteCount}, #{commentCount}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(CommunityPost post);

    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "COALESCE(pl_stats.like_count, 0) as like_count, " +
            "COALESCE(pf_stats.favorite_count, 0) as favorite_count, " +
            "COALESCE(pc_stats.comment_count, 0) as comment_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked, " +
            "COALESCE(user_favorite.is_favorited, FALSE) as is_favorited " +
            "FROM community_post cp " +
            "LEFT JOIN user u ON cp.user_id = u.id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as like_count FROM post_like WHERE is_liked = TRUE GROUP BY post_id) pl_stats ON cp.id = pl_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as favorite_count FROM post_favorite WHERE is_favorited = TRUE GROUP BY post_id) pf_stats ON cp.id = pf_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as comment_count FROM post_comment GROUP BY post_id) pc_stats ON cp.id = pc_stats.post_id " +
            "LEFT JOIN (SELECT post_id, is_liked FROM post_like WHERE user_id = #{currentUserId}) user_like ON cp.id = user_like.post_id " +
            "LEFT JOIN (SELECT post_id, is_favorited FROM post_favorite WHERE user_id = #{currentUserId}) user_favorite ON cp.id = user_favorite.post_id " +
            "ORDER BY cp.created_at DESC LIMIT #{size} OFFSET #{offset}")
    List<CommunityPost> selectAllWithStatsOrderByCreatedAtDescPaged(Long currentUserId, int offset, int size);

    // 保持向后兼容的方法（不使用分页）
    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "COALESCE(pl_stats.like_count, 0) as like_count, " +
            "COALESCE(pf_stats.favorite_count, 0) as favorite_count, " +
            "COALESCE(pc_stats.comment_count, 0) as comment_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked, " +
            "COALESCE(user_favorite.is_favorited, FALSE) as is_favorited " +
            "FROM community_post cp " +
            "LEFT JOIN user u ON cp.user_id = u.id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as like_count FROM post_like WHERE is_liked = TRUE GROUP BY post_id) pl_stats ON cp.id = pl_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as favorite_count FROM post_favorite WHERE is_favorited = TRUE GROUP BY post_id) pf_stats ON cp.id = pf_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as comment_count FROM post_comment GROUP BY post_id) pc_stats ON cp.id = pc_stats.post_id " +
            "LEFT JOIN (SELECT post_id, is_liked FROM post_like WHERE user_id = #{currentUserId}) user_like ON cp.id = user_like.post_id " +
            "LEFT JOIN (SELECT post_id, is_favorited FROM post_favorite WHERE user_id = #{currentUserId}) user_favorite ON cp.id = user_favorite.post_id " +
            "ORDER BY cp.created_at DESC")
    List<CommunityPost> selectAllWithStatsOrderByCreatedAtDesc(Long currentUserId);

    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "COALESCE(pl_stats.like_count, 0) as like_count, " +
            "COALESCE(pf_stats.favorite_count, 0) as favorite_count, " +
            "COALESCE(pc_stats.comment_count, 0) as comment_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked, " +
            "COALESCE(user_favorite.is_favorited, FALSE) as is_favorited " +
            "FROM community_post cp " +
            "LEFT JOIN user u ON cp.user_id = u.id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as like_count FROM post_like WHERE is_liked = TRUE GROUP BY post_id) pl_stats ON cp.id = pl_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as favorite_count FROM post_favorite WHERE is_favorited = TRUE GROUP BY post_id) pf_stats ON cp.id = pf_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as comment_count FROM post_comment GROUP BY post_id) pc_stats ON cp.id = pc_stats.post_id " +
            "LEFT JOIN (SELECT post_id, is_liked FROM post_like WHERE user_id = #{currentUserId}) user_like ON cp.id = user_like.post_id " +
            "LEFT JOIN (SELECT post_id, is_favorited FROM post_favorite WHERE user_id = #{currentUserId}) user_favorite ON cp.id = user_favorite.post_id " +
            "WHERE cp.user_id = #{userId} ORDER BY cp.created_at DESC")
    List<CommunityPost> selectByUserIdWithStatsOrderByCreatedAtDesc(Long userId, Long currentUserId);

    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "(SELECT COUNT(*) FROM post_like pl WHERE pl.post_id = cp.id AND pl.is_liked = TRUE) as like_count, " +
            "(SELECT COUNT(*) FROM post_favorite pf WHERE pf.post_id = cp.id AND pf.is_favorited = TRUE) as favorite_count, " +
            "(SELECT COUNT(*) FROM post_comment pc WHERE pc.post_id = cp.id) as comment_count, " +
            "(SELECT pl.is_liked FROM post_like pl WHERE pl.user_id = #{currentUserId} AND pl.post_id = cp.id) as is_liked, " +
            "(SELECT pf.is_favorited FROM post_favorite pf WHERE pf.user_id = #{currentUserId} AND pf.post_id = cp.id) as is_favorited " +
            "FROM community_post cp LEFT JOIN user u ON cp.user_id = u.id WHERE cp.user_id = #{userId} ORDER BY cp.created_at DESC LIMIT #{size} OFFSET #{offset}")
    List<CommunityPost> selectByUserIdWithStatsOrderByCreatedAtDescPaged(Long userId, Long currentUserId, int offset, int size);

    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "COALESCE(pl_stats.like_count, 0) as like_count, " +
            "COALESCE(pf_stats.favorite_count, 0) as favorite_count, " +
            "COALESCE(pc_stats.comment_count, 0) as comment_count, " +
            "COALESCE(user_like.is_liked, FALSE) as is_liked, " +
            "COALESCE(user_favorite.is_favorited, FALSE) as is_favorited " +
            "FROM community_post cp " +
            "LEFT JOIN user u ON cp.user_id = u.id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as like_count FROM post_like WHERE is_liked = TRUE GROUP BY post_id) pl_stats ON cp.id = pl_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as favorite_count FROM post_favorite WHERE is_favorited = TRUE GROUP BY post_id) pf_stats ON cp.id = pf_stats.post_id " +
            "LEFT JOIN (SELECT post_id, COUNT(*) as comment_count FROM post_comment GROUP BY post_id) pc_stats ON cp.id = pc_stats.post_id " +
            "LEFT JOIN (SELECT post_id, is_liked FROM post_like WHERE user_id = #{currentUserId}) user_like ON cp.id = user_like.post_id " +
            "LEFT JOIN (SELECT post_id, is_favorited FROM post_favorite WHERE user_id = #{currentUserId}) user_favorite ON cp.id = user_favorite.post_id " +
            "WHERE cp.id = #{id}")
    Optional<CommunityPost> selectByIdWithStats(Long id, Long currentUserId);

    @Select("SELECT * FROM community_post WHERE id = #{id}")
    Optional<CommunityPost> selectById(Long id);

    @Select("SELECT COUNT(*) FROM community_post WHERE user_id = #{userId}")
    int countByUserId(Long userId);

    @Select("SELECT cp.id, cp.user_id, cp.title, cp.content, cp.created_at, cp.updated_at, u.username as author, " +
            "(SELECT COUNT(*) FROM post_like pl WHERE pl.post_id = cp.id AND pl.is_liked = TRUE) as like_count, " +
            "(SELECT COUNT(*) FROM post_favorite pf WHERE pf.post_id = cp.id AND pf.is_favorited = TRUE) as favorite_count, " +
            "(SELECT COUNT(*) FROM post_comment pc WHERE pc.post_id = cp.id) as comment_count, " +
            "(SELECT pl.is_liked FROM post_like pl WHERE pl.user_id = #{currentUserId} AND pl.post_id = cp.id) as is_liked, " +
            "(SELECT pf.is_favorited FROM post_favorite pf WHERE pf.user_id = #{currentUserId} AND pf.post_id = cp.id) as is_favorited, " +
            "pf.created_at as favorite_time " +
            "FROM community_post cp " +
            "LEFT JOIN user u ON cp.user_id = u.id " +
            "INNER JOIN post_favorite pf ON cp.id = pf.post_id " +
            "WHERE pf.user_id = #{userId} AND pf.is_favorited = TRUE " +
            "ORDER BY pf.created_at DESC LIMIT #{size} OFFSET #{offset}")
    List<CommunityPost> selectFavoritesByUserIdWithStatsOrderByCreatedAtDescPaged(Long userId, Long currentUserId, int offset, int size);

    @Update("UPDATE community_post SET title = #{title}, content = #{content}, updated_at = #{updatedAt} WHERE id = #{id}")
    int updateById(CommunityPost post);

    @Update("UPDATE community_post SET like_count = #{likeCount} WHERE id = #{id}")
    int updateLikeCount(Long id, Integer likeCount);

    @Update("UPDATE community_post SET favorite_count = #{favoriteCount} WHERE id = #{id}")
    int updateFavoriteCount(Long id, Integer favoriteCount);

    @Update("UPDATE community_post SET comment_count = #{commentCount} WHERE id = #{id}")
    int updateCommentCount(Long id, Integer commentCount);

    @Delete("DELETE FROM community_post WHERE id = #{id}")
    int deleteById(Long id);

    @Delete("DELETE FROM community_post WHERE user_id = #{userId}")
    int deleteByUserId(Long userId);
}
